---------------------------------------------------
-- @file
-- @brief 数据库相关协助库
---------------------------------------------------

-- DBHelper 模块定义
local class = {}


-- 传入时间戳，获得相差天数
local function getDiffDate(old, now)
    if not old or not now then
        return 10
    end

    local told = os.date("*t", old)
    local tnew = os.date("*t", now)

    told.hour = 0; told.min = 0; told.sec = 0
    tnew.hour = 0; tnew.min = 0; tnew.sec = 0

    local diff = os.difftime(os.time(tnew), os.time(told))
    diff = diff / (24 * 60 * 60)
    diff = math.floor(diff)

    return diff
end

-- 去掉不合法的字符，避免干扰sql语句
local function trimSQL(text, length)
    if not text or type(text) ~= 'string' then
        return text
    end

    local pat = "^$()%[]*+?`'\"!;{}@";
    for k = 1, string.len(pat) do
        local one = string.sub(pat, k, k)
        one = "%" .. one
        text = string.gsub(text, one, '');
    end

    if length then
        text = string.sub(text, 1, length);
    end

    return text
end

-- 复制数据
local function copyData(field_values)
    local elements = {}

    if type(field_values) == 'table' then
        for k, v in pairs(field_values) do
            elements[k] = trimSQL(v)
        end
    else
        elements = field_values
    end

    return elements
end


-- 形成插入语句体
local function getInsertBody(elements)
    -- () values ()
    local fields = ''
    local values = ''

    for k, v in pairs(elements) do
        if fields == '' then
            fields = k
        else
            fields = fields .. string.format(", %s", k)
        end
        if type(v)=='boolean' then 
            if v then 
                v = 1
            else
                v = 0
            end
        end
        if values == '' then
            values = string.format("'%s'", v)
        else
            values = values .. string.format(", '%s'", v)
        end
    end

    local body = "(" .. fields .. ")" .. " VALUES " .. "(" .. values .. ")"

    return body
end

-- 形成更新语句体
local function getUpdateBody(elements)
    local body = ''
    for k, v in pairs(elements) do
        if type(v)=='boolean' then 
            if v then 
                v = 1
            else
                v = 0
            end
        end        
        if body == '' then
            body = string.format("%s='%s'", k, v)
        else
            body = body .. string.format(", %s='%s'", k, v)
        end
    end
    return body
end

local function getModifyBody(elements)
    local body = ''
    for k, v in pairs(elements) do        
        assert(type(v)=='number',"_______差值更新数据类型错误____"..k..","..v)    
        if v~=0 then     
            if body == '' then
                body = string.format("%s=%s + ", k, k).. v
            else
                body = body .. string.format(", %s=%s + ", k, k) .. v
            end
        end
    end
    return body
end

-- 形成选择语句体
local function getSelectBody(elements)
    local body = ''
    if type(elements) == 'table' then
        for _, v in pairs(elements) do
            if type(v)=='boolean' then 
                if v then 
                    v = 1
                else
                    v = 0
                end
            end            
            if body == '' then
                body = string.format("%s", v)
            else
                body = body .. string.format(", %s", v)
            end
        end
    elseif type(elements) == 'string' then
        body = elements
    end

    if body == '' then
        body = '*'
    end
    
    return body
end

local function getValue(values)
    for k, v in pairs(values) do 
        if type(v)=='boolean' then 
            if v then 
                v = 1
            else
                v = 0
            end
        end                
        return k,v
    end
end

-- 形成条件语句体(有顺序)
local function getWhereBody(elements)
    local body = ''
    
    --兼容旧的{user_id=1}方式
    if not elements[1] or type(elements[1])~='table' then
        for k, v in pairs(elements) do
            if body == '' then
                body = string.format("%s='%s'", k,v)
            else
                body = body .. string.format(" AND %s='%s'", k,v)
            end
        end
        return body
    end 
    --条件的写法为 {{user_id=1}}
    for _, values in pairs(elements) do
        if body == '' then
            body = string.format("%s='%s'", getValue(values))
        else
            body = body .. string.format(" AND %s='%s'", getValue(values))
        end
    end

    return body
end

-- 形成完整的插入语句
class.insertSql = function (table_name, field_values, where_field_values)
    assert(type(field_values) == "table", "this is field_values type not table")

    -- insert
    local elements = copyData(field_values)
    local insert_body = getInsertBody(elements)
    local sql = "INSERT " .. table_name .. insert_body

    if where_field_values then
        assert(type(where_field_values) == "table", "this is where_field_values type not table")
        -- where condition
        local elements = copyData(where_field_values)
        local where_body = getWhereBody(elements)
        sql = sql .. " WHERE " .. where_body
    end

    return sql
end

-- 形成完整的删除语句
class.deleteSql = function (table_name, where_field_values)
    
    assert(type(where_field_values) == "table", "this is where_field_values type not table")

    local elements = copyData(where_field_values)
    local where_body = getWhereBody(elements)
    local sql = "DELETE FROM " .. table_name .. " WHERE " .. where_body
    
    return sql
end

-- 形成完整的更新语句
class.updateSql = function (table_name, field_values, where_field_values)
    assert(type(field_values) == "table", "this is field_values type not table")

    local elements = copyData(field_values)
    local update_body = getUpdateBody(elements)
    local sql = "UPDATE " .. table_name .. " SET " .. update_body

    if where_field_values then
        assert(type(where_field_values) == "table", "this is where_field_values type not table")
     
        -- where condition
        local elements = copyData(where_field_values)
        local where_body = getWhereBody(elements)
        sql = sql .. " WHERE " .. where_body
    end
    return sql
end

-- 形成完整的选择语句
class.selectSql = function (table_name, field_values, where_field_values)

    local elements = copyData(field_values)
    local select_body = getSelectBody(elements)
    local sql = "SELECT " .. select_body .. " FROM " .. table_name

    if where_field_values then
        assert(type(where_field_values) == "table", "this is where_field_values type not table")

        
        -- where condition
        local elements = copyData(where_field_values)
        local where_body = getWhereBody(elements)
        sql = sql .. " WHERE " .. where_body
    end
    
    return sql
end

-- 形成完整的存在语句
class.isExistSql = function (table_name, where_field_values)
    -- print("_____where_field_values__",table_name,where_field_values)
    assert(type(where_field_values) == "table", "this is where_field_values type not table")
    
    local elements = copyData(where_field_values)
    local select_body = getSelectBody("1")
    local where_body = getWhereBody(elements)
    local sql = "SELECT " .. select_body .. " FROM " .. table_name .. " WHERE " .. where_body
    
    return sql
end

--差值更新
class.modifySql = function (table_name, field_values, where_field_values)
    assert(type(field_values) == "table", "this is field_values type not table")

    local elements = copyData(field_values)
    local update_body = getModifyBody(elements)
    local sql = "UPDATE " .. table_name .. " SET " .. update_body

    if where_field_values then
        assert(type(where_field_values) == "table", "this is where_field_values type not table")
        -- where condition
        local elements = copyData(where_field_values)
        local where_body = getWhereBody(elements)
        sql = sql .. " WHERE " .. where_body
    end
    return sql
end



-- local sql = class.selectSql("user", nil, nil)
-- local sql = class.insertSql("user", {username=true,password=134})
-- local sql = class.deleteSql("user", {{id=12},{xx="3214"}})
-- local sql = class.updateSql("user", {username='xxx'}, {id=12,xx="3214"})
-- local sql = class.modifySql("user", {username=22}, {{id=12},{xx="3214"}} )
-- local sql = class.isExistSql("user", {id=12,xx="3214"})
-- print(sql)


return class

